//-----------------------------------------------------------------------
//  This file is part of the Microsoft Code Samples.
// 
//  Copyright (C) Microsoft Corporation.  All rights reserved.
// 
//  This source code is intended only as a supplement to Microsoft
//  Development Tools and/or on-line documentation.  See these other
//  materials for detailed information regarding Microsoft code samples.
// 
//  THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY
//  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.
//-----------------------------------------------------------------------

using System;
using System.Threading;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Text;
using Microsoft.Samples.SqlServer;
using System.IO;

namespace Microsoft.Samples.SqlServer 
{
    public class OrderingService
    {
        string msgString = "";
        ArrayList infoList = new ArrayList();
        SqlTransaction tran = null;
        SqlConnection conn = null;


        #region Main Loop - ProcessOrders
        /*
         * Processes messages in the Ordering queue. In each loop through the out most loop, a new transaction
         * is started and a conversation group ID is locked by calling "GetConversationGroupID()". All messages with 
         * that conversation group ID is received. The state associated with the conversation group is also 
         * retrieved. A sub-loop than goes through all the messages received and processes them one by one, 
         * making changes to the state as necessary. At the end of the main loop the state is saved and 
         * the transaction is committed. 
         *
         * In general processing messages this way is more efficient than reading the messages one at a time,
         * especially in systems where network latency is significant. 
         */
        public void ProcessOrders()
        {
            try
            {
                // Connect to SQL Server
                conn = new SqlConnection("context connection = true");
                conn.Open();

                // Process messages.
                while (true)
                {
                    // Start a transaction to dequeue a message
                    tran = conn.BeginTransaction();

                    if (ReceiveAllMsg(conn, tran) == 0)
                    {
                        break;
                    }

                    tran.Commit();

                    // Wait for the committing transactions to finish.
                    Thread.Sleep(1000);
                }
            }
            catch (Exception e)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                throw e;
            }
            finally
            {
                // Close the connection.
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

        public int ReceiveAllMsg(SqlConnection conn, SqlTransaction tran)
        {
            int numOfMsg = 0;

            // Create a service object
            Service orderingService = new Service(Constants.OrderingService, conn, tran);

            // Set the FetchSize to 1 since we will receive one message at a time, i.e. use RECEIVE TOP(1)
            orderingService.FetchSize = 1;

            Conversation conversation = null;

            String body = null;
            String msgType = null;
            Guid dialogHandle = Guid.Empty;
            Guid conversationGroupID = Guid.Empty;
            Guid conversationGroupIDLast = Guid.Empty;
            State state = null;

            while (true)
            {
                if ((conversation = orderingService.GetConversation(conn, tran)) == null)
                {
                    break;
                }

                // Increase the number of messages received.
                numOfMsg++;

                Message message = conversation.Receive();
                msgType = message.Type;
                dialogHandle = conversation.Handle;
                conversationGroupID = message.ConversationGroupId;
                body = Utility.GetStringFromStream(message.Body);

                if (body != null)
                {
                    StoreMessage(body, false);
                }

                // Retrieve state information for a new conversation group.
                if (conversationGroupIDLast != conversationGroupID) 
                {
                    // Assign the value of conversationGroupID to conversationGroupIDLast.
                    conversationGroupIDLast = conversationGroupID;

                    state = GetOrderState(conversationGroupID);
                    if (state == null)
                    {
                        // Create a new state
                        msgString = "Creating new state";
                        LogItem logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        state = CreateState(conversationGroupID);
                    }
                }

                switch (msgType)
                {
                    #region switch section
                    case Constants.OrderingRequestMessageType:
                        msgString = "Received Ordering Request Message";
                        LogItem logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessOrderingRequest(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.PurchasingApproveMessageType:
                        msgString = "Received Purchasing Approve Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessPurchasingApprove(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.PurchasingAcceptMessageType:
                        msgString = "Received Purchasing Accept Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessPurchasingAccept(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.PurchasingDeliveryMessageType:
                        msgString = "Received Purchasing Delivery Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessPurchasingDelivery(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.ManufacturingAcceptMessageType:
                        msgString = "Received Manufacturing Accept Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessManufacturingAccept(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.ManufacturingDeliveryMessageType:
                        msgString = "Received Manufacturing Delivery Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessManufacturingDelivery(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.ShippingAcceptMessageType:
                        msgString = "Received Shipping Accept Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        ProcessShippingAccept(body, conversationGroupID, dialogHandle, state);
                        break;

                    case Constants.EndDialogMessageType:
                        msgString = "Received End Dialog Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        conversation.End(conn, tran);
                        break;

                    case Constants.ErrorMessageType:
                        msgString = "Received Error Message";
                        logItem = new LogItem("ProcessOrders", "", msgString);
                        infoList.Add(logItem);
                        conversation.EndWithError(1, msgString, conn, tran);
                        break;

                    default:
                        break;

                    #endregion
                }

                // Update the state information 
                UpdateOrderState(conversationGroupID, state);
            }

            return numOfMsg;
        }

        #endregion
        
        #region Process Ordering Request
        /*
         * This method processes incoming ordering request messages. Checks the inventory to 
         * make sure that all line items are in stock. If some items are out of stock buy items, 
         * it sends a Purchasing Request message to the Purchasing Service to restock the items. 
         * If all out of stock items are manufactured by Adventure Works and the required subcomponents
         * are in stock, send a Manufacturing Request message to Manufacturing Service to make the items.
         * If all items are in stock, send a Shipping Request message to the Shipping Service to ship 
         * the order. All messages that the Purchasing Service sends out will be related to the 
         * same conversation group as the incoming Ordering Request message.
         */
        private void ProcessOrderingRequest(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();

            doc.LoadXml(msg);

            XmlNamespaceManager nsmanager = new XmlNamespaceManager(doc.NameTable);

            nsmanager.AddNamespace("ordering", Constants.XmlOrderingRequestNamespace);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            list = doc.GetElementsByTagName(Constants.XmlCustomerID);
            string customerId = list.Item(0).InnerXml;

            list = doc.GetElementsByTagName(Constants.XmlOrderDate);
            string orderDate = list.Item(0).InnerXml;

            // Modify state information
            state.SalesOrderId = salesOrderId;
            state.CustomerId = customerId;
            state.OriginalDialogHandle = dialogHandle;
            state.OrderDate = orderDate;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessOrdering Request", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            // The number of products and subcomponents that needs to go into the Purchase Request message
            int num = 0;

            // This flag indicates if the order can be shipped. An order can be shipped as long as all line items
            // in the order are in stock, otherwise it can't be shipped.
            Boolean canBeShipped = true;

            // Process each line item one by one
            foreach (XmlNode item in doc.SelectNodes("/ordering:OrderingRequest/ordering:LineItem", nsmanager))
            {
                // Get line number, product ID and quantity of the line item
                string lineNumber = item.SelectSingleNode("ordering:" + Constants.XmlLineNumber, nsmanager).InnerText;
                int productId = Convert.ToInt32(item.SelectSingleNode("ordering:" + Constants.XmlProductID, nsmanager).InnerText);
                int quantity = Convert.ToInt32(item.SelectSingleNode("ordering:" + Constants.XmlQuantity, nsmanager).InnerText);

                // Insert line item to hash
                msgString = "Line Number = " + lineNumber + " ProductId = " + productId + " Quantity = " + quantity;
                logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                infoList.Add(logItem);
                
                msgString = "Add to ShippingList ProductId = " + productId + " Quantity = " + quantity;
                logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                infoList.Add(logItem);

                AddToList(state.shippingList, productId, new ListItem(quantity, Constants.Status.PendingShippingAccept.ToString()));

                // if the item is not out of stock, skip the record
                if (HasEnoughInStock(productId, quantity))
                {
                    // Debit the stock level 
                    DebitStockLevel(productId, quantity);
                    continue;
                }

                canBeShipped = false;

                // if the out of stock item is a buy item, add it to the hash skip the rest
                if (IsBuyItem(productId))
                {
                    num++;
                    msgString = "Add to PurchasingList ProductId = " + productId + " Quantity = " + quantity;
                    logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    AddToList(state.purchasingList, productId, new ListItem(quantity, Constants.Status.PendingPurchasingApproval.ToString()));
                    continue;
                }

                // if you reached here, the product is a out of stock make item
                msgString = "ProcessOrderingRequest: Add to ManufacturingList ProductId = " + productId + " Quantity = " + quantity;
                logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                infoList.Add(logItem);
                AddToList(state.manufacturingList, productId, new ListItem(quantity, Constants.Status.PendingManufacturingAccept.ToString()));

                Hashtable hash = PurchaseList(productId, quantity);

                // this keeps track of the number of subcomponents for this make item that is out of stock
                int count = 0;

                foreach (int prodId in hash.Keys)
                {
                    // Get the component and quantity that needs to be ordered
                    int component = prodId;
                    int neededQty = (int) hash[prodId];

                    // Add an entry to the hashtable
                    msgString = "Add subcomponent to PurchasingList component = " + component + " neededQuantity = " + neededQty;
                    logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    AddToList(state.purchasingList, component, new ListItem(quantity, Constants.Status.PendingPurchasingApproval.ToString()));
                    count++;
                    num++;
                }
            } // end going through the line items    

            if (canBeShipped)
            {
                // Send shipping message
                msgString = "---- Sending Shipping Request ----";
                logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                infoList.Add(logItem);
                
                state.Status = Constants.Status.PendingShippingAccept.ToString();
                // Update the order status
                UpdateOrderStatus(state, Convert.ToInt32(Constants.Status.PendingShippingAccept));

                // Create the outgoing Shipping Request message
                doc = BuildShippingRequestMessage(salesOrderId, customerId, state.shippingList);

                // Send the message
                Guid dh = PostShippingRequestMessage(doc, conversationGroupID);

                state.ToShippingDialogHandle = dh;
            }
            else // some line items are out of stock
            {
                // If the purchase list is empty, than all the subcomponents are in stock and the product
                // can be manufactured. 
                if (state.purchasingList.Count == 0)
                {
                    msgString = "---- Sending Manufacturing Request ----";
                    logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    state.Status = Constants.Status.PendingManufacturingAccept.ToString();

                    // Update the order status
                    UpdateOrderStatus(state, Convert.ToInt32(Constants.Status.PendingManufacturingAccept));
                    // Create the outgoing Manufacturing Request message
                    doc = BuildManufacturingRequestMessage(salesOrderId, state.manufacturingList);
                    // Send the message
                    Guid dh = PostManufacturingRequestMessage(doc, conversationGroupID);
                    state.ToManufacturingDialogHandle = dh;
                }

                if (state.purchasingList.Count > 0)
                {
                    msgString = "---- Sending Purchasing Request ----";
                    logItem = new LogItem("ProcessOrderingRequest", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    state.Status = Constants.Status.PendingPurchasingApproval.ToString();

                    // Update the order status
                    UpdateOrderStatus(state, Convert.ToInt32(Constants.Status.PendingPurchasingApproval));
                    // Create the outgoing Purchasing Request message
                    doc = BuildPurchasingRequestMessage(salesOrderId, state.purchasingList);
                    // Send the message
                    Guid dh = PostPurchasingRequestMessage(doc, conversationGroupID);
                    state.ToPurchasingDialogHandle = dh;
                }
            }
            return;
        }
    
        #endregion

        #region Process Purchasing Approve
        /*
         * This method processes incoming purchasing approve messages and updates state information.
         */
        private void ProcessPurchasingApprove(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(msg);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;
        
            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessPurchasingApprove", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            list = doc.GetElementsByTagName(Constants.XmlStatus);    
            string status = list.Item(0).InnerXml;

            list = doc.GetElementsByTagName(Constants.XmlTimeStamp);
            string timeStamp = list.Item(0).InnerXml;

            // ToDo: At this point code could be added to check that the previous state was "pending inventory check"

            // Modify state information 
            if (status.Equals(Constants.StatusApproved))
            {
                // Need to update the order status used by the front end UI
                UpdateOrderStatus(state, Convert.ToInt32(Constants.Status.PendingPurchasingAccept));

                // Update the state of the items in the purchasing list
                foreach (int prodId in state.purchasingList.Keys)
                {
                    ListItem tempItem = (ListItem)((state.purchasingList)[prodId]);
                    ChangeItemState(state.purchasingList, prodId, state, Constants.Status.PendingPurchasingAccept);
                }
            }
            else 
            {
                // TODO: This state is not supported yet
            }

            return;    
        }
    
        #endregion

        #region Process Purchasing Accept
        /*
         * This method processes incoming purchasing accept messages and updates state information.
         */
        private void ProcessPurchasingAccept(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(msg);
            XmlNamespaceManager nsmanager = new XmlNamespaceManager(doc.NameTable);
            nsmanager.AddNamespace("purchasingAccept", Constants.XmlPurchasingAcceptNamespace);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessPurchasingAccept", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            list = doc.GetElementsByTagName(Constants.XmlTimeStamp);
            string timeStamp = list.Item(0).InnerXml;

            foreach (XmlNode item in doc.SelectNodes("/purchasingAccept:PurchasingAccept/purchasingAccept:ProductDetail", nsmanager))
            {
                // Get individutal product detail items
                int productId = Convert.ToInt32(item.SelectSingleNode("purchasingAccept:" + Constants.XmlProductID, nsmanager).InnerText);
                int quantity = Convert.ToInt32(item.SelectSingleNode("purchasingAccept:" + Constants.XmlQuantity, nsmanager).InnerText);
                int estShipDate = Convert.ToInt32(item.SelectSingleNode("purchasingAccept:" + Constants.XmlEstimatedShipDate, nsmanager).InnerText);

                msgString = "Item product ID = " + productId + " new state = " + Constants.Status.PendingPurchasingDelivery.ToString();
                logItem = new LogItem("ProcessPurchasingAccept", state.SalesOrderId, msgString);
                infoList.Add(logItem);

                // Find the matching entry in the state 
                ChangeItemState(state.purchasingList, productId, state, Constants.Status.PendingPurchasingDelivery);                                                                        
            }

            // Todo: At this point code could be added to check that the previous state was "purchased approved"
            return;    
        }
    
        #endregion

        #region Process Purchasing Delivery
        /*
         * This method processes incoming purchasing delivery messages and updates state information.
         */
        private void ProcessPurchasingDelivery(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(msg);
            XmlNamespaceManager nsmanager = new XmlNamespaceManager(doc.NameTable);
            nsmanager.AddNamespace("purchasingDelivery", Constants.XmlPurchasingDeliveryNamespace);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessPurchasingDelivery", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            list = doc.GetElementsByTagName(Constants.XmlTimeStamp);

            string timeStamp = list.Item(0).InnerXml;

            foreach (XmlNode item in doc.SelectNodes("/purchasingDelivery:PurchasingDelivery/purchasingDelivery:ProductDetail", nsmanager))
            {
                // Get individutal product detail items
                int productId = Convert.ToInt32(item.SelectSingleNode("purchasingDelivery:" + Constants.XmlProductID, nsmanager).InnerText);
                int quantity = Convert.ToInt32(item.SelectSingleNode("purchasingDelivery:" + Constants.XmlQuantity, nsmanager).InnerText);


                // Find the matching entry in the state. The next state depends on if manufacturing work is needed.
                if (state.manufacturingList.Count != 0)
                {
                    msgString = "Item product ID = " + productId + " new state = " + Constants.Status.PendingManufacturingAccept.ToString();
                    logItem = new LogItem("ProcessPurchasingDelivery", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    ChangeItemState(state.purchasingList, productId, state, Constants.Status.PendingManufacturingAccept);
                }
                else
                {
                    msgString = "Item product ID = " + productId + "new state = " + Constants.Status.PendingShippingAccept.ToString(); ;
                    logItem = new LogItem("ProcessPurchasingDelivery", state.SalesOrderId, msgString);
                    infoList.Add(logItem);
                    ChangeItemState(state.purchasingList, productId, state, Constants.Status.PendingShippingAccept);
                }
            }

            // Decide whether manufacture or shipping is the next step
            if (state.Status.Equals(Constants.Status.PendingManufacturingAccept.ToString()))
            {
                // Send Manufacturing Request message
                msgString = "---- Sending Manufacturing Request ----";
                logItem = new LogItem("ProcessPurchasingDelivery", state.SalesOrderId, msgString);
                infoList.Add(logItem);
                doc = BuildManufacturingRequestMessage(salesOrderId, state.manufacturingList);

                // Send the message
                Guid dh = PostManufacturingRequestMessage(doc, conversationGroupID);
                state.ToManufacturingDialogHandle = dh;
            }
            else if (state.Status.Equals(Constants.Status.PendingShippingAccept.ToString()))
            {
                // Send shipping message
                msgString = "---- Sending Shipping Request ----";
                logItem = new LogItem("ProcessPurchasingDelivery", state.SalesOrderId, msgString);
                infoList.Add(logItem);
        
                // Create the outgoing Shipping Request message
                doc = BuildShippingRequestMessage(salesOrderId, state.CustomerId, state.shippingList);
                // Send the message
                Guid dh = PostShippingRequestMessage(doc, conversationGroupID);

                state.ToShippingDialogHandle = dh;
            }
    
            return;
        }
    
        #endregion

        #region Process Manufacturing Accept
        /*
         * This method processes incoming manufacturing accept messages and updates state information.
         */
        private void ProcessManufacturingAccept(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();

            doc.LoadXml(msg);

            XmlNamespaceManager nsmanager = new XmlNamespaceManager(doc.NameTable);

            nsmanager.AddNamespace("manufacturingAccept", Constants.XmlManufacturingAcceptNamespace);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessManufacturingAccept", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            list = doc.GetElementsByTagName(Constants.XmlTimeStamp);

            string timeStamp = list.Item(0).InnerXml;

            foreach (XmlNode item in doc.SelectNodes("/manufacturingAccept:ManufacturingAccept/manufacturingAccept:ProductDetail", nsmanager))
            {
                // Get individutal product detail items
                int productId = Convert.ToInt32(item.SelectSingleNode("manufacturingAccept:" + Constants.XmlProductID, nsmanager).InnerText);
                int quantity = Convert.ToInt32(item.SelectSingleNode("manufacturingAccept:" + Constants.XmlQuantity, nsmanager).InnerText);
                int estShipDate = Convert.ToInt32(item.SelectSingleNode("manufacturingAccept:" + Constants.XmlEstimatedShipDate, nsmanager).InnerText);

                msgString = "Item product ID = " + productId + " new state = " + Constants.Status.PendingManufacturingDelivery.ToString();
                logItem = new LogItem("ProcessManufacturingAccept", state.SalesOrderId, msgString);
                infoList.Add(logItem);

                // Find the matching entry in the state 
                ChangeItemState(state.manufacturingList, productId, state, Constants.Status.PendingManufacturingDelivery);
            }

            return;
        }
    
        #endregion

        #region Process Manufacturing Delivery
        /*
         * This method processes incoming manufacturing delivery messages and updates state information.
         */
        private void ProcessManufacturingDelivery(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();

            doc.LoadXml(msg);

            XmlNamespaceManager nsmanager = new XmlNamespaceManager(doc.NameTable);

            nsmanager.AddNamespace("manufacturingDelivery", Constants.XmlManufacturingDeliveryNamespace);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessManufacturingDelivery", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            list = doc.GetElementsByTagName(Constants.XmlTimeStamp);

            string timeStamp = list.Item(0).InnerXml;

            foreach (XmlNode item in doc.SelectNodes("/manufacturingDelivery:ManufacturingDelivery/manufacturingDelivery:ProductDetail", nsmanager))
            {
                // Get individutal product detail items
                int productId = Convert.ToInt32(item.SelectSingleNode("manufacturingDelivery:" + Constants.XmlProductID, nsmanager).InnerText);
                int quantity = Convert.ToInt32(item.SelectSingleNode("manufacturingDelivery:" + Constants.XmlQuantity, nsmanager).InnerText);

                msgString = "Item product ID = " + productId + " new state = " + Constants.Status.PendingShippingAccept.ToString();
                logItem = new LogItem("ProcessManufacturingDelivery", state.SalesOrderId, msgString);
                infoList.Add(logItem);

                // Find the matching entry in the state 
                ChangeItemState(state.manufacturingList, productId, state, Constants.Status.PendingShippingAccept);
            }

            if (state.Status.Equals(Constants.Status.PendingShippingAccept.ToString()))
            {
                // Send a shipping request to ship the item
                msgString = "---- Sending Shipping Request ----";
                logItem = new LogItem("ProcessManufacturingDelivery", state.SalesOrderId, msgString);
                infoList.Add(logItem);
                doc = BuildShippingRequestMessage(salesOrderId, state.CustomerId, state.shippingList);
                Guid dh = PostShippingRequestMessage(doc, conversationGroupID);
                state.ToShippingDialogHandle = dh;
            }

            return;
        }
    
        #endregion

        #region Process Shipping Accept
        /*
         * This method processes incoming shipping accept messages and updates state information.
         */
        private void ProcessShippingAccept(String msg, Guid conversationGroupID, Guid dialogHandle, State state)
        {            
            // Load XML msg into doc
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(msg);

            XmlNodeList list = doc.GetElementsByTagName(Constants.XmlSalesOrderID);
            string salesOrderId = list.Item(0).InnerXml;

            msgString = "Received message for sales order ID = " + salesOrderId;
            LogItem logItem = new LogItem("ProcessShippingAccept", state.SalesOrderId, msgString);
            infoList.Add(logItem);
            // At this point code could be added to check that the previous state was "pending shipping accept"

            foreach (int prodId in state.shippingList.Keys)
            {
                ListItem tempItem = (ListItem)((state.shippingList)[prodId]);
                ChangeItemState(state.shippingList, prodId, state, Constants.Status.Shipped);
            }

            state.Status = Constants.Status.Shipped.ToString();
            // Need to update the order status used by the front end UI
            UpdateOrderStatus(state, Convert.ToInt32(Constants.Status.Shipped));
            
            return;    
        }
    
        #endregion

        #region Build Shipping Request Message
        /*
         * Builds the Shipping Request message.
         */
        private XmlDocument BuildShippingRequestMessage(string salesOrderId, string customerId, Hashtable hash)
        {
            XmlDocument xd = new XmlDocument();
            XmlElement root = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlShippingRequestRoot, Constants.XmlShippingRequestNamespace);
            xd.AppendChild(root);
            
            XmlElement levelOne, levelTwo;

            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlSalesOrderID, Constants.XmlShippingRequestNamespace);
            levelOne.InnerText=salesOrderId.ToString();
            root.AppendChild(levelOne);

            // Insert the outer "Customer" tag
            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlCustomer, Constants.XmlShippingRequestNamespace);
            levelOne.InnerText=""; 
            root.AppendChild(levelOne);

            // Add the customer information
            SqlDataReader sdr = GetCustomerInfo(Convert.ToInt32(customerId));
            while (sdr.Read())
            {
                // Add first name
                string fName = Utility.GetStringFromReader(sdr, "FirstName");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlFirstName, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = fName;
                levelOne.AppendChild(levelTwo);

                // Add last name
                string lName = Utility.GetStringFromReader(sdr, "LastName");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlLastName, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = lName;
                levelOne.AppendChild(levelTwo);

                // Add address1
                string address1 =Utility.GetStringFromReader(sdr, "AddressLine1");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlAddress1, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = address1;
                levelOne.AppendChild(levelTwo);

                // Add address2
                string address2 = Utility.GetStringFromReader(sdr, "AddressLine2");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlAddress2, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = address2;
                levelOne.AppendChild(levelTwo);

                // Add city
                string city = Utility.GetStringFromReader(sdr, "City");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlCity, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = city;
                levelOne.AppendChild(levelTwo);

                // Add state
                string state =Utility.GetStringFromReader(sdr, "State");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlState, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = state;
                levelOne.AppendChild(levelTwo);

                // Add country
                string country = Utility.GetStringFromReader(sdr, "Country");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlCountry, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = country;
                levelOne.AppendChild(levelTwo);

                // Add zip
                string postalCode = Utility.GetStringFromReader(sdr, "PostalCode");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlZip, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = postalCode;
                levelOne.AppendChild(levelTwo);

                // Add phone
                string telephone =Utility.GetStringFromReader(sdr, "Telephone");
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlTelephone, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = telephone;
                levelOne.AppendChild(levelTwo);
            }

            sdr.Close();

            IDictionaryEnumerator en = hash.GetEnumerator();

            while (en.MoveNext())
            {
                // This tag encloses the line items
                levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductDetail, Constants.XmlShippingRequestNamespace);
                levelOne.InnerText=""; 
                root.AppendChild(levelOne);

                int productId = (int)en.Key;
                ListItem item = (ListItem)en.Value;
                int quantity = item.Quantity;
                
                // Add the XML for line items
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductID, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = productId.ToString();
                levelOne.AppendChild(levelTwo);

                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlQuantity, Constants.XmlShippingRequestNamespace);
                levelTwo.InnerText = quantity.ToString();
                levelOne.AppendChild(levelTwo);
            }
            
            return xd;
        }

        #endregion

        #region Post Shipping Request Message
        /*
         * Send the ShippingRequest message to the Shipping Service.
         */
        private  Guid PostShippingRequestMessage(XmlDocument xd, Guid conversationGroupID)
        {
            Service orderingService = new Service(Constants.OrderingService, conn, tran);

            Conversation dialog = orderingService.BeginDialog(
                Constants.ShippingService,
                null,
                Constants.ShippingContract,
                TimeSpan.FromSeconds(Convert.ToDouble(Constants.DialogTimeout)), false,
                conversationGroupID, conn, tran);

            string msgString = xd.InnerXml;
            MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(msgString));
            Message message = new Message(Constants.ShippingRequestMessageType, body);

            // Send the message to the service
            dialog.Send(message, conn, tran);

            // For audit, store the message.
            StoreMessage(msgString, true);

            return dialog.Handle;
        }    

        #endregion

        #region GetCustomerInfo
        /*
         * Gets customer information such as address and phone number.
         */
        private SqlDataReader GetCustomerInfo(int customerId)
        {
            SqlDataReader sdr = null;

            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetCustomerInfo";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 10);
            parameterCustomerID.Value = customerId;
            cmd.Parameters.Add(parameterCustomerID);

            // Execute the command
            sdr = cmd.ExecuteReader(CommandBehavior.Default);

            return sdr;
        }

        #endregion

        #region Post Manufacturing Request Message
        /*
         * Send the ManufacturingRequest message to the Manufacturing Service.
         */
        private  Guid PostManufacturingRequestMessage(XmlDocument xd, Guid conversationGroupID)
        {
            Service orderingService = new Service(Constants.OrderingService, conn, tran);

            Conversation dialog = orderingService.BeginDialog(
                Constants.ManufacturingService,
                null,
                Constants.ManufacturingContract,
                TimeSpan.FromSeconds(Convert.ToDouble(Constants.DialogTimeout)), false,
                conversationGroupID, conn, tran);

            string msgString = xd.InnerXml;
            MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(msgString));
            Message message = new Message(Constants.ManufacturingRequestMessageType, body);

            // Send the message to the service
            dialog.Send(message, conn, tran);

            // For audit, store the message.
            StoreMessage(msgString, true);

            return dialog.Handle;
        }    

        #endregion

        #region BuildManufacturingRequestMessage
        /*
         * Builds the Manufacturing Request message.
         */
        private XmlDocument BuildManufacturingRequestMessage(string salesOrderId, Hashtable hash)
        {
            XmlDocument xd = new XmlDocument();
            XmlElement root = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlManufacturingRequestRoot, Constants.XmlManufacturingRequestNamespace);
            xd.AppendChild(root);
            
            XmlElement levelOne, levelTwo;
            
            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlSalesOrderID, Constants.XmlManufacturingRequestNamespace);
            levelOne.InnerText=salesOrderId.ToString();
            root.AppendChild(levelOne);

            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlTimeStamp, Constants.XmlManufacturingRequestNamespace);
            levelOne.InnerText= DateTime.Now.ToString();
            root.AppendChild(levelOne);
                
            IDictionaryEnumerator en = hash.GetEnumerator();

            while (en.MoveNext())
            {
                // This tag encloses the line items
                levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductDetail, Constants.XmlManufacturingRequestNamespace);
                levelOne.InnerText=""; 
                root.AppendChild(levelOne);
                
                int productId = (int)en.Key;
                ListItem item = (ListItem)en.Value;
                int quantity = item.Quantity;

                // Add the XML for line items
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductID, Constants.XmlManufacturingRequestNamespace);
                levelTwo.InnerText = productId.ToString();
                levelOne.AppendChild(levelTwo);

                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlQuantity, Constants.XmlManufacturingRequestNamespace);
                levelTwo.InnerText = quantity.ToString();
                levelOne.AppendChild(levelTwo);
            }
            
            return xd;
        }

        #endregion

        #region Post Purchasing Request Message
        /*
         * Send the PurchasingRequest message to the Purchasing Service.
         */
        private Guid PostPurchasingRequestMessage(XmlDocument xd, Guid conversationGroupID)
        {
            
            Service orderingService = new Service(Constants.OrderingService, conn, tran);

            Conversation dialog = orderingService.BeginDialog(
                Constants.PurchasingService,
                null,
                Constants.PurchasingContract,
                TimeSpan.FromSeconds(Convert.ToDouble(Constants.DialogTimeout)), false,
                conversationGroupID, conn, tran);

            string msgString = xd.InnerXml;
            MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(msgString));
            Message message = new Message(Constants.PurchasingRequestMessageType, body);

            // Send the message to the service
            dialog.Send(message, conn, tran);

            // For audit, store the message.
            StoreMessage(msgString, true);

            return dialog.Handle;
        }    

        #endregion
        
        #region BuildPurchasingRequestMessage
        /*
         * Builds the Purchasing Request message.
         */
        private XmlDocument BuildPurchasingRequestMessage(string salesOrderId, Hashtable hash)
        {
            XmlDocument xd = new XmlDocument();
            XmlElement root = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlPurchasingRequestRoot, Constants.XmlPurchasingRequestNamespace);

            xd.AppendChild(root);

            XmlElement levelOne, levelTwo;

            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlSalesOrderID, Constants.XmlPurchasingRequestNamespace);
            levelOne.InnerText = salesOrderId.ToString();
            root.AppendChild(levelOne);
            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlTimeStamp, Constants.XmlPurchasingRequestNamespace);
            levelOne.InnerText = DateTime.Now.ToString();
            root.AppendChild(levelOne);
            levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlVendorID, Constants.XmlPurchasingRequestNamespace);
            levelOne.InnerText = "1";
            root.AppendChild(levelOne);

            IDictionaryEnumerator en = hash.GetEnumerator();

            while (en.MoveNext())
            {
                // This tag encloses the line items
                levelOne = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductDetail, Constants.XmlPurchasingRequestNamespace);
                levelOne.InnerText = "";
                root.AppendChild(levelOne);

                int productId = (int)en.Key;
                ListItem item = (ListItem)en.Value;
                int quantity = item.Quantity;

                // Add the XML for line items
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlProductID, Constants.XmlPurchasingRequestNamespace);
                levelTwo.InnerText = productId.ToString();
                levelOne.AppendChild(levelTwo);
                levelTwo = xd.CreateElement(Constants.XmlNamespacePrefix, Constants.XmlQuantity, Constants.XmlPurchasingRequestNamespace);
                levelTwo.InnerText = quantity.ToString();
                levelOne.AppendChild(levelTwo);
            }

            return xd;
        }

        #endregion

        #region Purchase List
        /*
         * Gets the list of items needed to be purchased to make this item.
         */
        private Hashtable PurchaseList(int productId, int requiredQuantity)
        {
            SqlDataReader sdr = null;

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetComponentPurchaseList";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductAssemblyID", SqlDbType.Int, 10);
            parameterProductID.Value = productId;
            cmd.Parameters.Add(parameterProductID);

            SqlParameter parameterQuantity = new SqlParameter("@QuantityNeeded", SqlDbType.Int, 50);
            parameterQuantity.Value = requiredQuantity;
            cmd.Parameters.Add(parameterQuantity);

            // Execute the command
            sdr = cmd.ExecuteReader(CommandBehavior.Default);

            // Read the list to close sdr
            Hashtable hash = new Hashtable();

            while (sdr.Read())
            {
                int component = Convert.ToInt32(sdr.GetValue(sdr.GetOrdinal("Component")).ToString());
                int neededQty = Convert.ToInt32(Convert.ToDouble(sdr.GetValue(sdr.GetOrdinal("NeededQty")).ToString()));

                if (hash.ContainsKey(component))
                {
                    int curQty = (int)hash[component];
                    //remove the old entry
                    hash.Remove(component);
                    hash.Add(component, curQty + neededQty);
                }
                else
                {
                    hash.Add(component, neededQty);
                }
            }

            sdr.Close();
            return hash;
        }

        #endregion

        #region Is Buy Item
        /*
         * Returns whether the product is a buy item.
         */
        private Boolean IsBuyItem(int productId)
        {
            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetMakeFlag";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 10);
            parameterProductID.Value = productId;
            cmd.Parameters.Add(parameterProductID);

            SqlParameter parameterMakeFlag = new SqlParameter("@MakeFlag", SqlDbType.Int, 4);
            parameterMakeFlag.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parameterMakeFlag);

            cmd.ExecuteNonQuery();

            // Get the quantity in stock for the product
            int makeFlag = (int)parameterMakeFlag.Value;

            return (makeFlag == 0);
        }

        #endregion

        #region Debit Stock Level
        /*
         * Debits the stock level of the product.
         */
        private void DebitStockLevel(int productId, int quantity)
        {
            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DebitStockLevel";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 10);
            parameterProductID.Value = productId;
            cmd.Parameters.Add(parameterProductID);

            SqlParameter parameterQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 50);
            parameterQuantity.Value = quantity;
            cmd.Parameters.Add(parameterQuantity);

            cmd.ExecuteNonQuery();
        }

        #endregion

        #region Has Enough In Stock
        /*
         * Returns a boolean indicating if there is enough product in stock.
         */
        private Boolean HasEnoughInStock(int productId, int requiredQuantity)
        {
            int stock = 0;

            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetStockLevel";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 10);
            parameterProductID.Value = productId;
            cmd.Parameters.Add(parameterProductID);

            SqlParameter parameterQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 50);
            parameterQuantity.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parameterQuantity);

            cmd.ExecuteNonQuery();

            // Get the quantity in stock for the product    
            stock = (int)parameterQuantity.Value;

            return (stock > requiredQuantity);
        }

        #endregion

        #region Add To List
        /*
         * Adds the item to the list.
         */
        private  void AddToList(Hashtable hash, int productId, ListItem item)
        {
            if (hash.ContainsKey(productId))
            {
                ListItem oldItem = (ListItem)hash[productId];
                int oldQty = oldItem.Quantity;
                //remove the old entry
                hash.Remove(productId);

                // Add to the quantity of the existing hash entry
                ListItem newItem = new ListItem(item.Quantity + oldQty, item.Status);
                hash.Add(productId, newItem);
            }
            else 
            {
                hash.Add(productId, item);
            }
        }
        #endregion

        #region Change Item State
        /*
        * Changes the state of an item in the hashtable.
        */
        private void ChangeItemState(Hashtable hash, int productId, State state, Constants.Status status)    
        {
            msgString = "ProductId = " + productId + " new state = " + status.ToString();
            LogItem logItem = new LogItem("ChangeItemState", state.SalesOrderId, msgString);
            infoList.Add(logItem);

            if (hash.ContainsKey(productId))
            {
                ListItem item = (ListItem)hash[productId];
                string curStatus = item.Status;
                int quantity = (int)item.Quantity;

                item.Status = status.ToString();
                
                // if all line items state flips, change the state of the order
                bool flip = true;
                foreach (int prodId in hash.Keys)
                {
                    ListItem tempItem = (ListItem)hash[prodId];
                    string tempStatus = tempItem.Status;

                    if (!tempStatus.Equals(status.ToString()))
                    {
                        // some line items have not changed to the new state yet
                        flip = false;
                        break;
                    }
                }

                if (flip)
                {
                    UpdateOrderStatus(state, Convert.ToInt32(status));
                    state.Status = status.ToString();
                }
            }
            else
            {
                msgString = "This is wrong!";
                logItem = new LogItem("UpdateItemState", state.SalesOrderId, msgString);
                infoList.Add(logItem);
            }
        }
        #endregion

        #region Create State
        /*
         * Create a new state.
         */
        private  State CreateState(Guid conversationGroupID)
        {
            State state = new State();
            state.ConversationGroupID = conversationGroupID;
            return state;
        }

        #endregion

        #region Get Order State
        /*
         * Retrieves the state information for an order.
         */
        private State GetOrderState(Guid conversationGroupID)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetOrderState";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter paramCG = new SqlParameter("@ConversationGroupId", SqlDbType.UniqueIdentifier);
            paramCG.Value = conversationGroupID;
            cmd.Parameters.Add(paramCG);

            State state = new State();
            // Execute the command
            try
            {
                // "using" gaurantees that the reader will close at end of block
                using (SqlDataReader result = cmd.ExecuteReader())
                {
                    if (result.Read())
                    {
                        state.OriginalDialogHandle = Utility.GetGuidFromReader(result, "OriginalDialogHandle");
                        state.ToPurchasingDialogHandle = Utility.GetGuidFromReader(result, "ToPurchasingDialogHandle");
                        state.ToShippingDialogHandle = Utility.GetGuidFromReader(result, "ToShippingDialogHandle");
                        state.ToManufacturingDialogHandle = Utility.GetGuidFromReader(result, "ToManufacturingDialogHandle");
                        state.CustomerId = Utility.GetStringFromReader(result, "CustomerId");
                        state.SalesOrderId = Utility.GetStringFromReader(result, "SalesOrderId");
                        state.OrderDate = Utility.GetStringFromReader(result, "OrderDate");
                        state.Status = Utility.GetStringFromReader(result, "Status");
                    }
                    else
                    {
                        state = null;
                    }

                    result.Close();
                    if (!(state == null))
                    {
                        GetItemState(state);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            return state;
        }

        #endregion

        #region Get Item State
        /*
         * Gets the state of the individual items in the purchasing, shipping and manufacturing list.
         */
        private State GetItemState(State state)
        {
            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "GetItemState";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter paramSalesOrderId = new SqlParameter("@SalesOrderId", SqlDbType.Int);
            paramSalesOrderId.Value = state.SalesOrderId;
            cmd.Parameters.Add(paramSalesOrderId);

            // Execute the command
            try
            {
                // "using" gaurantees that the reader will close at end of block
                using (SqlDataReader result = cmd.ExecuteReader())
                {
                    while (result.Read())
                    {
                        int productId = Utility.GetIntFromReader(result, "ProductId");
                        string type = Utility.GetStringFromReader(result, "Type");
                        int quantity = Utility.GetIntFromReader(result, "Quantity");
                        string status = Utility.GetStringFromReader(result, "Status");

                        if (type.Equals(Constants.Type.Purchasing.ToString()))
                        {
                            AddToList(state.purchasingList, productId, new ListItem(quantity, status));
                        }
                        else if (type.Equals(Constants.Type.Manufacturing.ToString()))
                        {
                            AddToList(state.manufacturingList, productId, new ListItem(quantity, status));
                        }
                        else if (type.Equals(Constants.Type.Shipping.ToString()))
                        {
                            AddToList(state.shippingList, productId, new ListItem(quantity, status));
                        }
                    }

                    result.Close();
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            return state;
        }

        #endregion

        #region Update Order State
        /*
         * Updates the state information in the database.
         */
        private void UpdateOrderState(Guid conversationGroupID, State state)
        {
            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "UpdateOrderState";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter paramSI = new SqlParameter("@ConversationGroupId", SqlDbType.UniqueIdentifier);
            paramSI.Value = conversationGroupID;
            cmd.Parameters.Add(paramSI);

            SqlParameter paramOriginalDialogHandle = new SqlParameter("@OriginalDialogHandle", SqlDbType.UniqueIdentifier);
            paramOriginalDialogHandle.Value = state.OriginalDialogHandle;
            cmd.Parameters.Add(paramOriginalDialogHandle);

            SqlParameter paramToPurchasingDialogHandle = new SqlParameter("@ToPurchasingDialogHandle", SqlDbType.UniqueIdentifier);
            paramToPurchasingDialogHandle.Value = state.ToPurchasingDialogHandle;
            cmd.Parameters.Add(paramToPurchasingDialogHandle);

            SqlParameter paramToShippingDialogHandle = new SqlParameter("@ToShippingDialogHandle", SqlDbType.UniqueIdentifier);
            paramToShippingDialogHandle.Value = state.ToShippingDialogHandle;
            cmd.Parameters.Add(paramToShippingDialogHandle);

            SqlParameter paramToManufacturingDialogHandle = new SqlParameter("@ToManufacturingDialogHandle", SqlDbType.UniqueIdentifier);
            paramToManufacturingDialogHandle.Value = state.ToManufacturingDialogHandle;
            cmd.Parameters.Add(paramToManufacturingDialogHandle);

            SqlParameter paramCustomerId = new SqlParameter("@CustomerId", SqlDbType.NVarChar, 50);
            paramCustomerId.Value = state.CustomerId;
            cmd.Parameters.Add(paramCustomerId);

            SqlParameter paramSalesOrderId = new SqlParameter("@SalesOrderId", SqlDbType.NVarChar, 50);
            paramSalesOrderId.Value = state.SalesOrderId;
            cmd.Parameters.Add(paramSalesOrderId);

            SqlParameter paramOrderDate = new SqlParameter("@OrderDate", SqlDbType.NVarChar, 50);
            paramOrderDate.Value = state.OrderDate;
            cmd.Parameters.Add(paramOrderDate);

            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            paramStatus.Value = state.Status;
            cmd.Parameters.Add(paramStatus);

            cmd.ExecuteNonQuery();
            Logger logger = new Logger();
            logger.StoreProcessingInfo(infoList, conn, tran);
            // Clear out the messages
            infoList = new ArrayList();

            // Save the hashed items in purchasing, manufacturing and shipping hash
            UpdateItemState(state, Constants.Type.Purchasing);
            UpdateItemState(state, Constants.Type.Manufacturing);
            UpdateItemState(state, Constants.Type.Shipping);

            return;
        }

        #endregion

        #region Update Item State
        /*
         * Updates the state of the individual items in the purchasing, shipping and manufacturing list.
         */
        private void UpdateItemState(State state, Constants.Type type)
        {
            Hashtable hash;
            string salesOrderId = state.SalesOrderId;

            if (type == Constants.Type.Purchasing)
            {
                hash = state.purchasingList;
            }
            else if (type == Constants.Type.Manufacturing)
            {
                hash = state.manufacturingList;
            }
            else if (type == Constants.Type.Shipping)
            {
                hash = state.shippingList;
            }
            else
            {
                hash = null;
            }

            foreach (int prodId in hash.Keys)
            {
                ListItem tempItem = (ListItem)hash[prodId];
                int quantity = tempItem.Quantity;
                string status = tempItem.Status.ToString();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UpdateItemState";
                cmd.Transaction = tran;

                // Mark the Command as a SPROC
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramSalesOrderId = new SqlParameter("@SalesOrderId", SqlDbType.Int);
                paramSalesOrderId.Value = salesOrderId;
                cmd.Parameters.Add(paramSalesOrderId);

                SqlParameter paramProductId = new SqlParameter("@ProductId", SqlDbType.Int);
                paramProductId.Value = prodId;
                cmd.Parameters.Add(paramProductId);

                SqlParameter paramType = new SqlParameter("@Type", SqlDbType.NVarChar, 50);
                paramType.Value = type.ToString();
                cmd.Parameters.Add(paramType);

                SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int);
                paramQuantity.Value = quantity;
                cmd.Parameters.Add(paramQuantity);

                SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
                paramStatus.Value = status;
                cmd.Parameters.Add(paramStatus);

                string text = cmd.CommandText;
                cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region Update Order Status
        /*
         * Updates the status of the sales order so it shows up on the UI.
         */
        private void UpdateOrderStatus(State state, int status)
        {
            int salesOrderId = Convert.ToInt32(state.SalesOrderId);
            state.Status = ((Constants.Status)(status)).ToString();

            // Create Instance of Connection and Command Object
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "UpdateOrderStatus";
            cmd.Transaction = tran;

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter paramSalesOrderId = new SqlParameter("@SalesOrderID", SqlDbType.Int);
            paramSalesOrderId.Value = salesOrderId;
            cmd.Parameters.Add(paramSalesOrderId);

            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int);
            paramStatus.Value = status;
            cmd.Parameters.Add(paramStatus);

            cmd.ExecuteNonQuery();

            return;
        }

        #endregion

        #region Store Message
        /*
         * Stores a message in the auditing table.
         */
        private void StoreMessage(string messageBody, Boolean isSent)
        {
            // Don't store empty string messages. These are system messages such as end dialog and error.
            if (messageBody.Equals(""))
                return;

            // Get the context command
            SqlCommand cmd = conn.CreateCommand();

            cmd.Transaction = tran;
            cmd.CommandText = "StoreMessage";

            // Mark the Command as a SPROC
            cmd.CommandType = CommandType.StoredProcedure;

            // Add message body
            SqlParameter paramMessageBody = new SqlParameter("@MessageBody", SqlDbType.NText);
            paramMessageBody.Value = messageBody;
            cmd.Parameters.Add(paramMessageBody);

            // Add direction
            SqlParameter paramDirection = new SqlParameter("@Direction", SqlDbType.NVarChar);

            if (isSent)
            {
                paramDirection.Value = "sent";
            }
            else
            {
                paramDirection.Value = "received";
            }

            cmd.Parameters.Add(paramDirection);
            cmd.ExecuteNonQuery();

            return;
        }

        #endregion

    }
}